package util;

import bean.SignUp;
import bean.TourRoute;
import bean.TourShift;
import bean.person;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SignUpDao {
    //增加
    public void addSignUp(SignUp sgup) throws SQLException {
        System.out.println("jin add");

        Connection conn = DbUtil.getConnection();
        //sql
        String sql="insert into SignUp"+"(signUpNo,userName,name,id,phone,routeNo,startPoint,endPoint,days,attraction,shiftNo,departureDate,tourismStandard,quote,discountRate,cost)"+"values("+"'"+sgup.getSignUpNo()+"'"+","+"'"+sgup.getUserName()+"'"+","+"'"+sgup.getName()+"'"+","+"'"+sgup.getId()+"'"+","+"'"+sgup.getPhone()+"'"+","+"'"+sgup.getRouteNo()+"'"+","+"'"+sgup.getStartPoint()+"'"+","+"'"+sgup.getEndPoint()+"'"+","+"'"+sgup.getDays()+"'"+","+"'"+sgup.getAttraction()+"'"+","+"'"+sgup.getShiftNo()+"'"+","+"'"+sgup.getDepartureDate()+"'"+","+"'"+sgup.getTourismStandard()+"'"+","+"'"+sgup.getQuote()+"'"+","+"'"+sgup.getDiscountRate()+"'"+","+"'"+sgup.getCost()+"'"+")";
        System.out.println(sql);
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.execute();
        //更新编号
        System.out.println(sgup.getSignUpNo());
        ptmt = conn.prepareStatement("UPDATE UPDATNO set signUpNo='"+sgup.getSignUpNo()+"' WHERE No='1'");//更新
        ptmt.execute();
        System.out.println("add signup success!");
    }

    //删除(signUpNo)
    public void delSignUp(String signUpNo) throws SQLException {
        Connection conn = DbUtil.getConnection();
        String sql = "delete from SignUp where signUpNo='"+signUpNo+"'";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.execute();
    }

    //列出所有报名信息
    public List<SignUp> query() throws SQLException {
        Connection conn = DbUtil.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select * from  SignUp");
        List<SignUp> SgupList = new ArrayList<SignUp>();
        SignUp sgup = null;
        while (rs.next()){
            sgup = new SignUp();
            sgup.setSignUpNo(rs.getString("signUpNo"));
            sgup.setUserName(rs.getString("userName"));
            sgup.setName(rs.getString("name"));
            sgup.setId(rs.getString("id"));
            sgup.setPhone(rs.getString("phone"));
            sgup.setRouteNo(rs.getString( "routeNo"));
            sgup.setStartPoint(rs.getString("startPoint"));
            sgup.setEndPoint(rs.getString("endPoint"));
            sgup.setDays(rs.getInt("days"));
            sgup.setAttraction(rs.getString("attraction"));
            sgup.setShiftNo(rs.getString( "shiftNo"));
            sgup.setDepartureDate(rs.getString("departureDate"));
            sgup.setTourismStandard(rs.getString("tourismStandard"));
            sgup.setQuote(rs.getInt("quote"));
            sgup.setDiscountRate(rs.getFloat("discountRate"));
            sgup.setCost(rs.getFloat("cost"));

            SgupList.add(sgup);
        }
        return SgupList;
    }

    //update
    public void updateSignUp(String signUpNo,String phone,String routeNo,String shiftNo) throws SQLException {
        Connection conn = DbUtil.getConnection();
        TourRouteDao trdao = new TourRouteDao();
        TourShiftDao tsdao = new TourShiftDao();
        TourRoute tr = null;
        TourShift ts = null;
        tr = trdao.get(routeNo);
        ts = tsdao.get(shiftNo);
        System.out.println(tr+"--"+ts);
        System.out.println("cost = "+ts.getQuote()+"*"+ts.getDiscountRate()+"="+ts.getQuote()*ts.getDiscountRate());
        float cost = ts.getQuote()*ts.getDiscountRate();
        String sql = "UPDATE SignUp set phone='"+phone+"',routeNo='"+routeNo+"',startPoint='"+tr.getStartPoint()+"',endPoint='"+tr.getEndPoint()+"',days='"+tr.getDays()+"',attraction='"+tr.getAttraction()+"',shiftNo='"+shiftNo+"',departureDate='"+ts.getDepartureDate()+"',tourismStandard='"+ts.getTourismStandard()+"',quote='"+ts.getQuote()+"',discountRate='"+ts.getDiscountRate()+"',cost='"+cost+"' where signUpNo='"+signUpNo+"'";
        System.out.println(sql);
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.execute();
        System.out.println("update signup finish!");
    }

    //搜索框
    public List<SignUp> search(String find) throws SQLException {
        Connection conn = DbUtil.getConnection();
        List<SignUp> SgupList = new ArrayList<SignUp>();
        Statement stmt = conn.createStatement();
        String[] sql = new String[16];
        sql[0] = "select * from  SignUp where signUpNo='"+find+"'";
        sql[1] = "select * from  SignUp where userName='"+find+"'";
        sql[2] = "select * from  SignUp where name='"+find+"'";
        sql[3] = "select * from  SignUp where id='"+find+"'";
        sql[4] = "select * from  SignUp where phone='"+find+"'";
        sql[5] = "select * from  SignUp where routeNo='"+find+"'";
        sql[6] = "select * from  SignUp where startPoint='"+find+"'";
        sql[7] = "select * from  SignUp where endPoint='"+find+"'";
        sql[8] = "select * from  SignUp where days='"+find+"'";
        sql[9] = "select * from  SignUp where attraction='"+find+"'";
        sql[10] = "select * from  SignUp where shiftNo='"+find+"'";
        sql[11] = "select * from  SignUp where departureDate='"+find+"'";
        sql[12] = "select * from  SignUp where tourismStandard='"+find+"'";
        sql[13] = "select * from  SignUp where quote='"+find+"'";
        sql[14] = "select * from  SignUp where discountRate='"+find+"'";
        sql[15] = "select * from  SignUp where cost='"+find+"'";
        int flag = DbUtil.isNum(find);
        System.out.println("find="+find+",flag="+flag);
        for (int i=0;i<16;i++){         //0字符串，1小数，2整数
            if (flag==1){
                if (i==13||i==8)
                continue;
            } else if (flag==0){
                if (i==13 || i==14 || i==15 || i==8)
                    continue;
            }else { }
            ResultSet rs = stmt.executeQuery(sql[i]);
            System.out.println(rs);
            while (rs.next()){
                SignUp sgup = new SignUp();
                sgup.setSignUpNo(rs.getString("signUpNo"));
                sgup.setUserName(rs.getString("userName"));
                sgup.setName(rs.getString("name"));
                sgup.setId(rs.getString("id"));
                sgup.setPhone(rs.getString("phone"));
                sgup.setRouteNo(rs.getString( "routeNo"));
                sgup.setStartPoint(rs.getString("startPoint"));
                sgup.setEndPoint(rs.getString("endPoint"));
                sgup.setDays(rs.getInt("days"));
                sgup.setAttraction(rs.getString("attraction"));
                sgup.setShiftNo(rs.getString( "shiftNo"));
                sgup.setDepartureDate(rs.getString("departureDate"));
                sgup.setTourismStandard(rs.getString("tourismStandard"));
                sgup.setQuote(rs.getInt("quote"));
                sgup.setDiscountRate(rs.getFloat("discountRate"));
                sgup.setCost(rs.getFloat("cost"));
                System.out.println("sgup="+sgup.getSignUpNo());
                SgupList.add(sgup);
            }
        }
        System.out.println(SgupList.size());
        return SgupList;
    }

    public String updateSignUpNo() throws SQLException {
        //获取已存在的最大（新）staffNo
        String signUpNo = null;
        Connection conn = DbUtil.getConnection();
        String sql = "select * from  UPDATNO";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ResultSet rs = ptmt.executeQuery();
        while (rs.next()){
            signUpNo = rs.getString("signUpNo");
        }
        char[] one = signUpNo.toCharArray();
        char first = one[0];
        String two = signUpNo.substring(1);
        int num = Integer.parseInt(two);
        num = num+1;
        System.out.println(one + "-" + two);
        signUpNo = first + Integer.toString(num);

        System.out.println(signUpNo);

        return signUpNo;
    }
}
